package ro.prjmanager.core.service;

import org.hibernate.Query;
import org.hibernate.Session;
import ro.prjmanager.core.dao.Filter;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@SuppressWarnings({"unchecked"})
public class SQLQuery {
    private Class clazz;
    private List<Filter> filters;
    private List<Order> orders;
    private List<Join> joins;
    private Session hibernateSession;
    private int startPage;
    private int resPerPage;


    public SQLQuery(Session hibernateSession) {
        this.hibernateSession = hibernateSession;
    }

    public SQLQuery() {
    }

    public SQLQuery(int startPage, int resPerPage, List<Order> orders, List<Filter> filters) {
        this.startPage = startPage;
        this.resPerPage = resPerPage;
        this.orders = orders;
        this.filters = filters;
    }

    public Class getClazz() {
        return clazz;
    }

    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }

    public List<Filter> getFilters() {
        return filters;
    }

    public void setFilters(List<Filter> filters) {
        this.filters = filters;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    public Session getHibernateSession() {
        return hibernateSession;
    }

    public void setHibernateSession(Session hibernateSession) {
        this.hibernateSession = hibernateSession;
    }

    private String getSql() {
        StringBuilder ret = new StringBuilder();
        ret.append("select c from ").append(clazz.getName()).append(" as c");

        //join clause
        StringBuilder joinClause = processJoin();
        if (joinClause != null)
            ret.append(joinClause);

        //where clauses
        StringBuilder filterClause = processWhere();
        if (filterClause != null)
            ret.append(filterClause);

        //order clauses
        StringBuilder orderClause = processOrders();
        if (orderClause != null)
            ret.append(orderClause);


        return ret.toString();
    }

    private String getSqlNo() {
        StringBuilder ret = new StringBuilder();
        ret.append("select count(*) from ").append(clazz.getName()).append(" as c");

        //join clause
        StringBuilder joinClause = processJoin();
        if (joinClause != null)
            ret.append(joinClause);

        //where clauses
        StringBuilder filterClause = processWhere();
        if (filterClause != null)
            ret.append(filterClause);




        return ret.toString();
    }




    private StringBuilder processJoin() {
        StringBuilder sb = null;
        if (joins != null && joins.size() > 0) {
            sb = new StringBuilder();
            for (Join join : joins) {
//                sb.append(join.getAlias() != null && join.getAlias().length() > 0 ? join.getAlias() : "");
                sb.append(" join ");
                sb.append(" ").append(join.getProperty());
                if(join.getAlias()!=null&&join.getAlias().length()>0)
                    sb.append(" as ").append(join.getAlias());
            }
        }
        return sb;
    }

    public static final String FILTER_PREFIX = "filt";

    private StringBuilder buildForIn(Filter filter) {
        StringBuilder ret = null;
        ArrayList<Object> inFilter = (ArrayList<Object>) filter.getPropertyValue();
        if (filter.isList()) {
            ret = new StringBuilder();
            for (int p = 0; p < inFilter.size(); p++) {
                Object inFilterStr = inFilter.get(p);
                if (inFilterStr != null)
                    ret.append(":").append(FILTER_PREFIX).append(p).append(filter.getPropertyName());

            }
        }
        return ret;
    }

    private StringBuilder processWhere() {
        StringBuilder ret = null;
        if (filters != null && filters.size() > 0) {
            ret = new StringBuilder();
            int start = 0;
            for (Filter filter : filters) {
                if (start == 0)
                    ret.append(" where ");
                else ret.append(" and ");
                ret.append(filter.getAlias()).append(".").append(filter.getPropertyName());
                String op = filter.getOpp();
                if (op != null && op.length() > 0) {
                    if (op.equals("="))
                        ret.append(" =:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    else if (op.equals(">"))
                        ret.append(" >:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    else if (op.equals("<"))
                        ret.append(" <:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    else if (op.equals(">="))
                        ret.append(" >=:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    else if (op.equals("<="))
                        ret.append(" <=:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    else if (op.equals("in"))
                        ret.append(" in(").append(buildForIn(filter)).append(" ) ");
                    else {
                        ret.append(" =:").append(FILTER_PREFIX).append(filter.getPropertyName());
                    }
                }
                start++;
            }
        }
        return ret;
    }

    private StringBuilder processOrders() {
        StringBuilder ret = null;
        if (orders != null && orders.size() > 0) {
            int startOrd = 0;
            ret = new StringBuilder();
            for (Order order : orders) {
                if (startOrd == 0)
                    ret.append(" order by ");
                else ret.append(" , ");
                ret.append(order.getAlias()).append(".").append(order.getProperty());
                ret.append(order.isAsc() ? " asc " : " desc ");
                startOrd++;
            }
        }

        return ret;
    }

    public int getStartPage() {
        return startPage;
    }

    public void setStartPage(int startPage) {
        this.startPage = startPage;
    }

    public int getResPerPage() {
        return resPerPage;
    }

    public void setResPerPage(int resPerPage) {
        this.resPerPage = resPerPage;
    }

    private void bindFiltersValues(Query query) {
        if (filters == null || filters.size() == 0)
            return;
        for (Filter filter : filters) {
            String pref = FILTER_PREFIX + filter.getPropertyName();
            if(filter.getOpp().equals("in")){
                bindFiltersValuesForInOpp(query,filter);

            }else{
                if (filter.isBigDecimal())
                    query.setBigDecimal(pref, (BigDecimal) filter.getPropertyValue());
                else if (filter.isDate())
                    query.setDate(pref, (Date) filter.getPropertyValue());
                else if (filter.isInteger())
                    query.setInteger(pref, (Integer) filter.getPropertyValue());
                else if (filter.isLong())
                    query.setLong(pref, (Long) filter.getPropertyValue());
                else if (filter.isString())
                    query.setString(pref, (String) filter.getPropertyValue());
            }

        }
    }

    private void bindFiltersValuesForInOpp(Query query, Filter filter) {
        ArrayList<Object> inFilters = (ArrayList<Object>) filter.getPropertyValue();
        int p=0;
        for(Object inFilterObj:inFilters){
            String pref = FILTER_PREFIX+p + filter.getPropertyName();
            if (filter.isBigDecimal())
                query.setBigDecimal(pref, (BigDecimal) inFilterObj);
            else if (filter.isDate())
                query.setDate(pref, (Date) inFilterObj);
            else if (filter.isInteger())
                query.setInteger(pref, (Integer) inFilterObj);
            else if (filter.isLong())
                query.setLong(pref, (Long) inFilterObj);
            else if (filter.isString())
                query.setString(pref, (String) inFilterObj);
            p++;
        }
    }

    public Query getQuery() {
        String sql = getSql();
        if (sql == null)
            return null;
        Query ret = getHibernateSession().createQuery(sql);
        //binding filters values
        bindFiltersValues(ret);
        ret.setMaxResults(resPerPage);
        ret.setFirstResult(startPage);
        return ret;
    }

    public Query getQueryNo() {
        String sql = getSqlNo();
        if (sql == null)
            return null;
        Query ret = getHibernateSession().createQuery(sql);
        //binding filters values
        bindFiltersValues(ret);

        return ret;
    }



    public boolean addFilter(Filter filter) {
        if(filters==null) filters=new ArrayList<Filter>();
        return filters.add(filter);
    }

    public boolean addOrder(Order order) {
        if(orders==null)orders=new ArrayList<Order>();
        return orders.add(order);
    }

    public boolean addJoin(Join join) {
        if(joins==null) joins=new ArrayList<Join>();
        return joins.add(join);
    }

    public List<Join> getJoins() {
        return joins;
    }

    public void setJoins(List<Join> joins) {
        this.joins = joins;
    }
}
